<html>
<head>
<title>Discuz! to phpBB 论坛数据转移工具</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8" /> 
</head>
<body>
<?php
/*
Discuz! to phpBB 论坛数据转移工具

注意:在导入表数据前，必须先手动清空初始安装时插入的数据,即
TRUNCATE TABLE `表名`:
TRUNCATE TABLE `phpbb_forums`,
TRUNCATE TABLE `phpbb_posts`,
TRUNCATE TABLE `phpbb_topics`,用户表 phpbb_users` 除外。
此外，各个板块的权限需手动调整.默认为游客权限.
*/

$ip = "127.0.0.1";
$pot = 3306;
$usr = "root";
$psw = "xzx";
$from_db = "discuz";
$to_db = "phpbb";
$i = 0;
$mysqli = new mysqli($ip,$usr,$psw,$from_db,$pot);
$str = "show databases;";
$user_permissions_from_anonymous = "00000000003khra3nk\ni1cjyo000000\ni1cjyo000000";
$k = 0;
define('FORUMS_TABLE','phpbb_forums');
define('FORUM_CAT', 0);
define('FORUM_POST', 1);
define('FORUM_LINK', 2);

//来自 phpBB3\includes\acp\acp_forums.php update_forum_data 函数部分,有修改
function getLeftAndRightID($parent_id){
			// no forum_id means we're creating a new forum
			global $mysqli,$to_db;
			$mysqli->select_db($to_db);

			if ($parent_id != 0)
			{
				$sql = 'SELECT left_id, right_id, forum_type
					FROM ' . FORUMS_TABLE . '
					WHERE forum_id = ' . $parent_id;

				$row = $mysqli->query($sql)->fetch_array();

				if (!$row)
				{
					echo "empty";
					return array(0,0);
				}

				if ($row['forum_type'] == FORUM_LINK)
				{
					echo "impossible";
					return array(0,0);
				}

				$sql = 'UPDATE ' . FORUMS_TABLE . '
					SET left_id = left_id + 2, right_id = right_id + 2
					WHERE left_id > ' . $row['right_id'];
				$mysqli->query($sql);

				$sql = 'UPDATE ' . FORUMS_TABLE . '
					SET right_id = right_id + 2
					WHERE ' . $row['left_id'] . ' BETWEEN left_id AND right_id';
				$mysqli->query($sql);

				$left_id = $row['right_id'];
				$right_id = $row['right_id'] + 1;
			}
			else
			{
				$sql = 'SELECT MAX(right_id) AS right_id
					FROM ' . FORUMS_TABLE;
				$row = $mysqli->query($sql)->fetch_array();

				$left_id = $row['right_id'] + 1;
				$right_id = $row['right_id'] + 2;
			}
			return array($left_id,$right_id);
}

echo "MySQL version is ".$mysqli->server_info."<br/><hr>";
//修正MySQL编码问题
if($mysqli->character_set_name() != "utf8")	$mysqli->set_charset("utf8"); echo "默认编码不是UTF-8,已改。<br/>";

//查询 cdb_members
//$str = "SELECT * FROM `cdb_members`"; //$str = "SELECT * FROM `uc_members`"; //查询ucenter 会员表

//查询 cdb_forums 和 cdb_forumfields
//$str = "SELECT * FROM `cdb_forums` a,`cdb_forumfields` b where a.fid = b.fid";

//查询 cdb_posts
//$str = "SELECT * FROM `cdb_posts`";

//查询 cdb_threads
//$str = "SELECT * FROM `cdb_threads`";

if ($mysqli->multi_query($str)) {
	do {
		$result = $mysqli->store_result();
		while ($row = $result->fetch_row()) {

			//转移 cdb_members 至 phpbb_users
			//user_type 定义，见 phpBB3\includes\constants.php 45 - 48 行
			//0 is for normal users
			//1 is for deactivated/inactive users
			//2 is for bots and anonymous user
			//3 is founder user

			//同步插入用户对组表.
			/*$mysqli->select_db($to_db);
			$str = "SELECT MAX( user_id ) FROM `phpbb_users`";
			$r = $mysqli->query($str)->fetch_array();
			$str = "INSERT INTO `phpbb_user_group`(`group_id`, `user_id`, `group_leader`, `user_pending`) VALUES (2,".($r[0]+1).",0,0)";
			$mysqli->query($str);*/

			//$str = "INSERT INTO `phpbb_users`(`user_type`, `group_id`, `user_permissions`, `user_ip`, `user_regdate`, `username`, `username_clean`, `user_password`, `user_email`, `user_birthday`, `user_lastvisit`, `user_lastpost_time`, `user_posts`, `user_lang`, `user_timezone`, `user_dateformat`, `user_colour`, `user_sig`, `user_occ`, `user_interests`, `user_new`) VALUES (0,2,'".$user_permissions_from_anonymous."','".($row[6] == 'hidden' ? '127.0.0.1' : $row[6])."',".$row[10].",'".$row[1]."','".strtolower($row[1])."','".$row[2]."','".$row[29]."','".$row[30]."',".$row[12].",".$row[14].",".$row[15].",'zh_cmn_hans',8.00,'Y年 M j日 H:i','9E8DA7','无签名','无描述','无兴趣',0)";

			//$str = "INSERT INTO `phpbb_users`(`user_type`, `group_id`, `user_permissions`, `user_ip`, `user_regdate`, `username`, `username_clean`, `user_password`, `user_email`, `user_sig`, `user_occ`, `user_interests`) VALUES (0,2,'".$user_permissions_from_anonymous."','".($row[6] == 'hidden' ? '127.0.0.1' : $row[6])."',".$row[7].",'".$row[1]."','".strtolower($row[1])."','".$row[2]."','".$row[3]."','无签名','无描述','无兴趣')"; //适用于 uc_members 的插入语句

			//转移 cdb_forums 和 cdb_forumfields 至 phpbb_forums
			//关于表结构，参见phpBB官方WIKI：http://wiki.phpbb.com/Tables 以及 phpBB3\install\schemas\mysql_41_schema.sql 文件
			/*$tmp = preg_split('/\s/', (trim($row[10]) == '' ? '000 无新帖 1280140622 无' : $row[10]));
			$j = count($tmp);
			preg_match_all('/(?<=\d\d\d\s).*(?=\s\d\d\d\d\d\d\d\d\d\d\s)/',($row[10] == '' ? '000 无新帖 1280140622 无' : $row[10]), $matches);
			$str = "SELECT user_id FROM `phpbb_users` WHERE username ='".$tmp[$j-1]."'";
			$mysqli->select_db($to_db);
			$r = $mysqli->query($str)->fetch_array();
			$ids = getLeftAndRightID($row[1]);*/

			//$str = "INSERT INTO `phpbb_forums`(`forum_id`, `parent_id`, `left_id`, `right_id`, `forum_parents`, `forum_name`, `forum_desc`, `forum_link`, `forum_password`, `forum_rules`, `forum_type`, `forum_posts`, `forum_topics`, `forum_topics_real`, `forum_last_post_id`, `forum_last_poster_id`, `forum_last_post_subject`, `forum_last_post_time`, `forum_last_poster_name`, `forum_last_poster_colour`, `forum_flags`, `enable_icons`) VALUES (".$row[0].",".$row[1].",".$ids[0].",".$ids[1].",' ','".$row[3]."','".$row[36]."','".$row[44]."','".$row[37]."','".$row[48]."',".($row[44] == '' ? ($row[2] == 'group' ? 0 : 1) : 2).",".$row[7].",".$row[8].",".$row[8].",".($row[2] == 'group' || $row[2] == 'sub' ? '0' : $tmp[0]).",".($row[2] == 'group' || $row[2] == 'sub' ? '0' : (empty($r[0]) ? 0 : $r[0])).",'".($row[2] == 'group' || $row[2] == 'sub' ? '' : $matches[0][0])."',".($row[2] == 'group' || $row[2] == 'sub' ? '0' : $tmp[$j-2]).",'".($row[2] == 'group' || $row[2] == 'sub' ? '' : $tmp[$j-1])."','AA0000',".($row[2] == 'group' || $row[2] == 'sub' ? 16 : 32).",".$row[11].")";

			//转移 cdb_posts 至 phpbb_posts
			/*$mysqli->select_db($to_db);
			$str = "SELECT user_id FROM `phpbb_users` WHERE username ='".$row[4]."'";
			$r_poster = $mysqli->query($str)->fetch_array();
			$mysqli->select_db($from_db);
			$str = "SELECT iconid FROM `cdb_threads` where tid = ".$row[0];
			$r_post_icon = $mysqli->query($str)->fetch_array();*/

			//$str = "INSERT INTO `phpbb_posts`(`post_id`, `topic_id`, `forum_id`, `poster_id`, `icon_id`, `poster_ip`, `post_time`, `enable_bbcode`, `enable_smilies`, `enable_magic_url`, `enable_sig`, `post_username`, `post_subject`, `post_text`, `post_attachment`) VALUES (".$row[0].",".$row[2].",".$row[1].",".$r_poster[0].",".($r_post_icon[0] == '' ? 0 : $r_post_icon[0]).",'".$row[9]."',".$row[7].",".($row[14] == -1 ? 1 : 0).",".($row[15] == -1 ? 1 : 0).",".($row[16] == -1 ? 1 : 0).",".$row[12].",'".$row[4]."','".$row[6]."','".$mysqli->real_escape_string($row[8])."',".$row[17].")";

			//转移 cdb_threads 至 phpbb_topics
			/*$mysqli->select_db($to_db);
			$str = "SELECT user_id FROM `phpbb_users` WHERE username ='".$row[7]."'";
			$r_poster = $mysqli->query($str)->fetch_array();
			$str = "SELECT user_id FROM `phpbb_users` WHERE username ='".$row[12]."'";
			$r_poster_l = $mysqli->query($str)->fetch_array();
			$str = "SELECT post_id,post_subject FROM `phpbb_posts` WHERE `topic_id` = ".$row[0]." AND `post_approved` = 1 ORDER BY `phpbb_posts`.`post_id` DESC";
			$r_post_l = $mysqli->query($str)->fetch_array();*/

			//$str = "INSERT INTO `phpbb_topics`(`topic_id`, `forum_id`, `icon_id`, `topic_attachment`, `topic_title`, `topic_poster`, `topic_time`, `topic_views`, `topic_replies`, `topic_replies_real`, `topic_status`, `topic_type`, `topic_first_post_id`, `topic_first_poster_name`, `topic_first_poster_colour`, `topic_last_post_id`, `topic_last_poster_id`, `topic_last_poster_name`, `topic_last_poster_colour`, `topic_last_post_subject`, `topic_last_post_time`, `topic_last_view_time`) VALUES (".$row[0].",".$row[1].",".$row[2].",".$row[20].",'".$row[9]."',".$r_poster[0].",".$row[10].",".$row[13].",".$row[14].",".$row[14].",".$row[28].",".$row[3].",".$row[0].",'".$row[7]."','AA0000',".($r_post_l['post_id'] == 0 ? 0 : $r_post_l['post_id']).",".$r_poster_l[0].",'".$row[12]."','AA0000','".($r_post_l['post_subject'] == '' ? '无回复' : $r_post_l['post_subject'])."',".$row[11].",".$row[11].")";

			$mysqli->select_db($to_db);
			if ($mysqli->query($str)){
				$i++;
			}else{
				//打印出有问题的SQL
				echo $str."<br/><br/>";
			}
			$k++;
		}
		$result->free();
	} while ($mysqli->more_results());
}
echo "<hr>";
echo "<h1>成功插入".$i."条数,一共".$k."条记录,失败".($k-$i)."条</h1>";
$mysqli->close();
?>
</body>
</html>
